package com.cs307.redcross.server;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.Scanner;
import java.util.Vector;

import com.cs307.redcross.client.Administrator;
import com.cs307.redcross.client.Event;
import com.cs307.redcross.client.EventTemplate;
import com.cs307.redcross.client.FreeTimeEvent;
import com.cs307.redcross.client.Group;
import com.cs307.redcross.client.LogFileEntry;
import com.cs307.redcross.client.Person;
import com.cs307.redcross.client.Reminder;
import com.cs307.redcross.client.ReminderTemplate;
import com.cs307.redcross.client.Volunteer;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

public class DatabaseHandler extends RemoteServiceServlet implements
		Serializable, com.cs307.redcross.client.DatabaseHandlerService{
	/**
	 * 
	 */
	public static final long serialVersionUID = 1L;
	public static DatabaseHandler instance = null; // singleton instance
	 public final static String propFile =
	 "src/com/cs307/redcross/server/database.properties123";
	public static int sleepTime = 1000;
	private  SecureRandom rand;
	private boolean idOverflow = false;
	// info about the database
	public String url;
	public String password;
	public String username;
	public Connection connection;
	public DateFormat dateFormat;
	public int sessionLeaseTime; //time for sessions in minutes
	public int nextID;
	public static boolean change;
	public static String[] tables = { "admins", "events", "freeTimeEvents",
			"eventTemplates", "groups", "volunteers", "reminders", "reminderTemplates", "logFileEntries", "tempUsers" };

	// this is a singleton, so the constructor is public
	public DatabaseHandler() throws Exception {
		try{
			Properties props = new Properties();
			FileInputStream in = new FileInputStream(propFile);
			props.load(in);
			in.close();
			url = props.getProperty("url2");
			password = props.getProperty("password");
			username = props.getProperty("username");
			sleepTime = Integer.parseInt(props.getProperty("sleepTime"));
		}catch(FileNotFoundException e)
		{
			System.out.println("File not found error caught");

			//url = "jdbc:mysql://128.211.204.97/redcross";
			url = "jdbc:mysql://localhost/redcross";

			username = "root";
			password = "";
			username = "cs307";
			password = "73528291sq";
			//password = "*623FF2554FFF22F1C820FB1CC5DA6C86220130FF";
			sleepTime = 15000;
			sessionLeaseTime = 120;
		}
		rand = new SecureRandom();
		System.out.println("connecting to " + url + " with username " + username);
		dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(url, username, password);
			// "jdbc:mysql://localhost/redcross", "cs307", "73528291sq");
			// connection = DriverManager.getConnection(
			// "jdbc:mysql://128.211.204.97/REDCROSS", "cs307", "73528291sq");
		}
		catch (SQLException e) {
			
			throw new Exception("SQL Exception: " + e.getMessage());
		}

		System.out.println("connection established");
		nextID = this.getNextAvailableID();
		System.out.println("nextID set to: " + nextID);

		change = true;
	}

	public void startDatabaseHandler() throws Exception {
		if (instance == null)
			instance = new DatabaseHandler();
	}

	// creates the Database handler
	public static DatabaseHandler create() throws Exception {
		try {
			if (instance == null)
				instance = new DatabaseHandler();
		}
		catch (SQLException e) {
			
			throw new Exception("SQL Exception: " + e.getMessage());
		}

		return instance;
	}

	public int getNextID() throws Exception {
		if(!idOverflow && nextID < 2147483646)
		{
			nextID++;
		}
		else
		{
			idOverflow = true;
			nextID = rand.nextInt();
			while(!isIdAvailable(nextID))
				nextID = rand.nextInt();
		}
		return nextID;
	}
	
	private boolean isIdAvailable(int id) throws Exception
	{
		for(String table : tables)
		{
			if (table == null)
				continue;
			
			try{
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM " + table +" WHERE id=" + id);
			if(result.next())
				return false;
			}catch(Exception e)
			{
				instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
				return false;
			}
		}
		return true;
	}

	public String clean(String s) {
		return s.toString().replace("'", "''").replace("\\", "\\\\")
				.replace("--", "\\-\\-");
	}

	public void recreateDB() throws Exception {
		try {
			Statement stat = connection.createStatement();
			instance = create();
			// Delete the table first if any

			try {
				stat.executeUpdate("DROP TABLE admins");
				stat.executeUpdate("DROP TABLE events");
				stat.executeUpdate("DROP TABLE freeTimeEvents");
				stat.executeUpdate("DROP TABLE eventTemplates");
				stat.executeUpdate("DROP TABLE groups");
				stat.executeUpdate("DROP TABLE volunteers");
				stat.executeUpdate("DROP TABLE reminders");
				stat.executeUpdate("DROP TABLE reminderTemplates");
				stat.executeUpdate("DROP TABLE logFileEntries");
				stat.executeUpdate("DROP TABLE tempUsers");
				stat.executeUpdate("DROP TABLE sessions");
			}
			catch (SQLException e2) {
				// this will catch the exception that gets thrown when no table
				// exists in the first place
				//instance.write(new LogFileEntry(-2, "server", e2.getStackTrace().toString(), true));
			}

			// Create the tables
			stat.executeUpdate("CREATE TABLE admins (id INT, firstName VARCHAR(100), lastName VARCHAR(100), email VARCHAR(512), carrier VARCHAR(100), cellPhoneNumber VARCHAR(100), smsEmail VARCHAR(512), username VARCHAR(512), password VARCHAR(512), FreeTimeEventList VARCHAR(10000), notifyOnNewMember INT)");
			stat.executeUpdate("CREATE TABLE events (id INT, name VARCHAR(512), description TEXT, startTime DATETIME, endTime DATETIME, recurrence VARCHAR(1000), nextEventID INT, groupIDList VARCHAR(10000), personIDList VARCHAR(10000))");
			stat.executeUpdate("CREATE TABLE freeTimeEvents (id INT, name VARCHAR(512), startTime DATETIME, endTime DATETIME, recurrence VARCHAR(1000), nextEventID INT, personID INT)");
			stat.executeUpdate("CREATE TABLE eventTemplates (id INT, name VARCHAR(512), description TEXT, startTime DATETIME, endTime DATETIME, recurrence VARCHAR(1000), groupIDList VARCHAR(10000), personIDList VARCHAR(10000))");
			stat.executeUpdate("CREATE TABLE groups (id INT, name VARCHAR(512), personIDList VARCHAR(10000), description TEXT, isPublic INT)");
			stat.executeUpdate("CREATE TABLE volunteers (id INT, firstName VARCHAR(100), lastName VARCHAR(100), email VARCHAR(512), carrier VARCHAR(100), cellPhoneNumber VARCHAR(100), smsEmail VARCHAR(512), username VARCHAR(512), password VARCHAR(512), FreeTimeEventList VARCHAR(10000))");
			stat.executeUpdate("CREATE TABLE reminders (id INT, eventID INT, time DATETIME, groupIDList VARCHAR(10000), personIDList VARCHAR(10000), message TEXT, sent INT, email INT, SMS INT)");
			stat.executeUpdate("CREATE TABLE reminderTemplates (id INT, name VARCHAR(1000), time INT, groupIDList VARCHAR(10000), personIDList VARCHAR(10000), message TEXT, email INT, SMS INT)");
			stat.executeUpdate("CREATE TABLE logFileEntries (id INT, sessionID INT, user VARCHAR(512), time DATETIME, logAction TEXT, developerOnly INT)");
			stat.executeUpdate("CREATE TABLE tempUsers (id INT, firstName VARCHAR(100), lastName VARCHAR(100), email VARCHAR(512), carrier VARCHAR(100), cellPhoneNumber VARCHAR(100), smsEmail VARCHAR(512), username VARCHAR(512), password VARCHAR(512), FreeTimeEventList VARCHAR(10000), regCode VARCHAR(1000))");
			stat.executeUpdate("CREATE TABLE sessions (sessionID INT, username VARCHAR(512), timeStarted DATETIME, timeExpired DATETIME)");

			instance = create();
			Administrator dev = new Administrator( "dev", "dev", "dev", "att", "dev", "dev", "dev", "redcross", new Vector<FreeTimeEvent>());
			write(dev);
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// check log-in info
	public Boolean validUser(String s) throws Exception {
		s = clean(s);
		Statement stat;
		try {
			stat = connection.createStatement();

			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE username LIKE '"
							+ s + "'");
			if (result.next()) {
				return true;
			}
			else {
				result = stat
						.executeQuery("SELECT * FROM volunteers WHERE username LIKE '"
								+ s + "'");
				if (result.next()) {
					return true;
				}
			}
			return false;

		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	/**
	 * This method causes a user to become logged in if the combination is valid, and it returns a session ID.  Otherwise
	 * it returns -1.
	 */
	public int validPassword(String user, String password) throws Exception {
		try {
			user = clean(user).toLowerCase();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE username LIKE '"
							+ user + "'");
			//System.out.println("user: " + user + "| pass: " + password);
			if (result.next()) {
				if (result.getString("password").equals(password))
				{
					//System.out.println("from server: " + password + "| from user: " + result.getString("password"));
					return logIn(user);
				}
			}
			else {
				result = stat
						.executeQuery("SELECT * FROM volunteers WHERE username LIKE '"
								+ user + "'");
				if (result.next()) {
					if (result.getString("password").equals(password))
					{
						//System.out.println("from server: " + password + "| from user: " + result.getString("password"));
						return logIn(user);
					}
				}
			}
			return -1;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	private String encrypt(String password ) throws Exception
	{
		 String algorithm = "SHA";

	        byte[] plainText = password.getBytes();

	        MessageDigest md = null;

	        try {		
	            md = MessageDigest.getInstance(algorithm);
	        } catch (Exception e) {
	        	instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
	            e.printStackTrace();
	        }
			
	        md.reset();		
	        md.update(plainText);
	        byte[] encodedPassword = md.digest();

	        StringBuilder sb = new StringBuilder();
	        for (int i = 0; i < encodedPassword.length; i++) {
	            if ((encodedPassword[i] & 0xff) < 0x10) {
	                sb.append("0");
	            }

	            sb.append(Long.toString(encodedPassword[i] & 0xff, 16));
	        }
		return sb.toString();
	}
	
	public boolean forgotPassword(String user) throws Exception
	{
		try{
			Person forgetter = getPerson(user.toLowerCase());
			if(forgetter==null) return false;
			String newPass = new BigInteger(130, rand).toString(16).substring(0,10);
			
			forgetter.setPassword(newPass);
			if(forgetter instanceof Administrator)
				instance.write((Administrator)forgetter);
			else if(forgetter instanceof Volunteer)
				instance.write((Volunteer)forgetter);
			
			String message = "Hello " + forgetter.getFirstName()+ ",\n" +
			"\n" +
			"This is a password reset for " + forgetter.getUsername() + ".\n" +
			"New password: " + newPass + "\n" +
			"\n" +
			"NOTE: If you are not the intended recipient of this message, please delete it immediately.";
			Vector<Person> temp = new Vector<Person>();
			temp.add(forgetter);
			Mail.sendeMail(temp, message, "Red Cross Reminder System Password Recovery");
			
			return true;
		}catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	
	private int logIn(String user) throws Exception
	{
		try{
			Statement stat = connection.createStatement();
			
			int sessionID = rand.nextInt(1000000000);
			ResultSet result = stat.executeQuery("SELECT * FROM sessions WHERE sessionID=" + sessionID);
			while(result.next())
			{
				sessionID = rand.nextInt(1000000000);
				result = stat.executeQuery("SELECT * FROM sessions WHERE sessionID=" + sessionID);
			}		
			Date now = new Date();
			Date expire = new Date(now.getTime() + sessionLeaseTime * 60 * 1000);
			stat.executeUpdate("INSERT INTO sessions VALUES ('" + sessionID
					+ "','" + clean(user) 
					+ "','"	+ dateFormat.format(now)
					+ "','"	+ dateFormat.format(expire)
					+ "')");
			return sessionID;
		}catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public boolean checkWithServerIfSessionIdIsStillLegal(int sessionID) throws Exception
	{
		try{
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM sessions WHERE timeExpired > CURRENT_TIMESTAMP AND sessionID=" + sessionID);
			if(result.next())
				return true;
			return false;
		}catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public Person getUserFromSessionId(int sessionID) throws Exception
	{
		try{
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM sessions WHERE sessionID=" + sessionID);
			if(result.next())
				return getPerson(result.getString("username"));
			return null;
		}catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// methods to check if an object is in the DB by checking the item's id
	public Boolean inDB(Administrator a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Boolean inDB(Event a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM events WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Boolean inDB(FreeTimeEvent a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM freeTimeEvents WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Boolean inDB(EventTemplate a) throws Exception {
		try {

			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM eventTemplates WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Boolean inDB(Group a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM groups WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Boolean inDB(Volunteer a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM volunteers WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Boolean inDB(Reminder a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminders WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public Boolean inDB(ReminderTemplate a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminderTemplates WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next())
				return true;
			return false;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// get items
	public Person getPerson(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE id LIKE '" + id
							+ "'");
			if (result.next()) {
				Administrator res = null;
				Vector<FreeTimeEvent> ft = getFreeTimeEvents(result
						.getString("FreeTimeEventList"));
				res = new Administrator(
						result.getString("firstName"),
						result.getString("lastName"),
						result.getString("email"), result.getString("carrier"),
						result.getString("cellPhoneNumber"),
						result.getString("smsEmail"),
						result.getString("username"),
						result.getString("password"), ft, true);
				res.setNotifyOnNewMember(Integer.parseInt(result
						.getString("notifyOnNewMember")) == 1);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}
			result = stat
					.executeQuery("SELECT * FROM volunteers WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				Volunteer res = null;
				Vector<FreeTimeEvent> ft = getFreeTimeEvents(result
						.getString("FreeTimeEventList"));
				res = new Volunteer(
						result.getString("firstName"),
						result.getString("lastName"),
						result.getString("email"), result.getString("carrier"),
						result.getString("cellPhoneNumber"),
						result.getString("smsEmail"),
						result.getString("username"),
						result.getString("password"), ft, true);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}
			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Person getPerson(String user) throws Exception {
		try {
			user = clean(user).toLowerCase();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE username LIKE '"
							+ user + "'");
			if (result.next()) {
				Administrator res = null;
				Vector<FreeTimeEvent> ft = getFreeTimeEvents(result
						.getString("FreeTimeEventList"));
				res = new Administrator(
						result.getString("firstName"),
						result.getString("lastName"),
						result.getString("email"), result.getString("carrier"),
						result.getString("cellPhoneNumber"),
						result.getString("smsEmail"),
						result.getString("username"),
						result.getString("password"), ft, true);
				res.setNotifyOnNewMember(Integer.parseInt(result
						.getString("notifyOnNewMember")) == 1);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}
			result = stat
					.executeQuery("SELECT * FROM volunteers WHERE username LIKE '"
							+ user + "'");
			if (result.next()) {
				Volunteer res = null;
				Vector<FreeTimeEvent> ft = getFreeTimeEvents(result
						.getString("FreeTimeEventList"));
				res = new Volunteer(
						result.getString("firstName"),
						result.getString("lastName"),
						result.getString("email"), result.getString("carrier"),
						result.getString("cellPhoneNumber"),
						result.getString("smsEmail"),
						result.getString("username"),
						result.getString("password"), ft, true);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}
			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Administrator getAdmin(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE id LIKE '" + id
							+ "'");
			if (result.next()) {
				Administrator res = null;
				Vector<FreeTimeEvent> ft = getFreeTimeEvents(result
						.getString("FreeTimeEventList"));
				res = new Administrator(
						
						result.getString("firstName"),
						result.getString("lastName"),
						result.getString("email"), result.getString("carrier"),
						result.getString("cellPhoneNumber"),
						result.getString("smsEmail"),
						result.getString("username"),
						result.getString("password"), ft, true);
				res.setNotifyOnNewMember(Integer.parseInt(result
						.getString("notifyOnNewMember")) == 1);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}
			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Volunteer getVolunteer(int id) throws Exception {
		return getVolunteer(id, false);
	}
	
	public Volunteer getVolunteer(int id, boolean fromTemp) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result;
			if(fromTemp)
			result = stat
					.executeQuery("SELECT * FROM tempUsers WHERE id LIKE '"
							+ id + "'");
			else
				result = stat
				.executeQuery("SELECT * FROM volunteers WHERE id LIKE '"
						+ id + "'");
			if (result.next()) {
				Volunteer res = null;
				Vector<FreeTimeEvent> ft = getFreeTimeEvents(result
						.getString("FreeTimeEventList"));
				res = new Volunteer(
						result.getString("firstName"),
						result.getString("lastName"),
						result.getString("email"), result.getString("carrier"),
						result.getString("cellPhoneNumber"),
						result.getString("smsEmail"),
						result.getString("username"),
						result.getString("password"), ft, true);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}
			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Event getEvent(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM events WHERE id LIKE '" + id
							+ "'");
			if (result.next()) {
				java.util.Date s = new java.util.Date(
						(result.getTimestamp("startTime")).getTime());
				java.util.Date e = new java.util.Date(
						(result.getTimestamp("endTime")).getTime());
				Vector<Group> g = getGroups(result.getString("groupIDList"));
				Vector<Person> p = getPeople(result.getString("personIDList"));
				Event res = new Event(
						result.getString("name"), result.getString("description"), s, e,
						result.getString("recurrence"), Integer.parseInt(result
								.getString("nextEventID")), g, p);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public FreeTimeEvent getFreeTimeEvent(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM freeTimeEvents WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				java.util.Date s = new java.util.Date(
						(result.getTimestamp("startTime")).getTime());
				java.util.Date e = new java.util.Date(
						(result.getTimestamp("endTime")).getTime());
				int p = Integer.parseInt(result.getString("personID"));
				FreeTimeEvent res = new FreeTimeEvent( p, result.getString("name"), s, e,
						result.getString("recurrence"), Integer.parseInt(result
								.getString("nextEventID")));
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public FreeTimeEvent getFreeTimeEvent(int id, Person p) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM freeTimeEvents WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				java.util.Date s = new java.util.Date(
						(result.getTimestamp("startTime")).getTime());
				java.util.Date e = new java.util.Date(
						(result.getTimestamp("endTime")).getTime());
				// Person p =
				// getPerson(Integer.parseInt(result.getString("personID")));
				FreeTimeEvent res = new FreeTimeEvent( p.getID(), result.getString("name"),
						s, e, result.getString("recurrence"),
						Integer.parseInt(result.getString("nextEventID")));
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public EventTemplate getEventTemplate(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM eventTemplates WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				java.util.Date s = new java.util.Date(
						(result.getTimestamp("startTime")).getTime());
				java.util.Date e = new java.util.Date(
						(result.getTimestamp("endTime")).getTime());
				Vector<Group> g = getGroups(result.getString("groupIDList"));
				Vector<Person> p = getPeople(result.getString("personIDList"));
				EventTemplate res = new EventTemplate( result.getString("name"), result.getString("description"), s, e,
						result.getString("recurrence"), g, p);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Group getGroup(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM groups WHERE id LIKE '" + id
							+ "'");
			if (result.next()) {
				Group res = new Group(
						result.getString("name"),
						getPeople(result.getString("personIDList")), result.getString("description"), false);
				res.setId(Integer.parseInt(result.getString("id")));
				res.setPublic(result.getInt("isPublic")==1);
				return res;
			}
			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Group getGroup(String name) throws Exception {
		try {
			name = clean(name);
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM groups WHERE name LIKE '"
							+ name + "'");
			if (result.next()) {
				Group res = new Group(
						result.getString("name"),
						getPeople(result.getString("personIDList")), result.getString("description"), false);
				res.setId(Integer.parseInt(result.getString("id")));
				res.setPublic(result.getInt("isPublic")==1);
				return res;
			}
			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Reminder getReminder(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminders WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				java.util.Date s = new java.util.Date(
						(result.getTimestamp("time")).getTime());
				Vector<Group> g = getGroups(result.getString("groupIDList"));
				Vector<Person> p = getPeople(result.getString("personIDList"));
				Reminder res = new Reminder( getEvent(Integer.parseInt(result
						.getString("eventID"))), s, g, p,
						result.getString("message"));
				res.setId(Integer.parseInt(result.getString("id")));
				if (result.getInt("sent") == 1)	res.setSent(true);
				if (result.getInt("email") == 1)	res.setEmail(true);
				if (result.getInt("SMS") == 1)	res.setSMS(true);
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public ReminderTemplate getReminderTemplate(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminderTemplates WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				Vector<Group> g = getGroups(result.getString("groupIDList"));
				Vector<Person> p = getPeople(result.getString("personIDList"));
				ReminderTemplate res = new ReminderTemplate( result.getString("name"), result.getInt("time"), g, p,
						result.getString("message"));
				res.setId(Integer.parseInt(result.getString("id")));
				if (result.getInt("email") == 1)	res.setEmail(true);
				if (result.getInt("SMS") == 1)	res.setSMS(true);
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public LogFileEntry getLogFileEntry(int id) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM logFileEntries WHERE id LIKE '"
							+ id + "'");
			if (result.next()) {
				java.util.Date s = new java.util.Date(
						(result.getTimestamp("time")).getTime());
				LogFileEntry res = new LogFileEntry(result.getInt("sessionID"), result.getString("user"), result.getString("logAction"), result.getInt("developerOnly")==1);
				res.setTime(s);
				res.setId(Integer.parseInt(result.getString("id")));
				return res;
			}

			return null;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// get lists of people and groups
	public Vector<Person> getPeople(String list) throws Exception {
		try {
			Vector<Person> people = new Vector<Person>();
			Scanner myScan = new Scanner(list);
			while (myScan.hasNext()) {
				String s = myScan.next();
				s = s.substring(0, s.length() - 1);
				int i = Integer.parseInt(s);
				Person temp = getPerson(i);
				people.add(temp);
			}
			return people;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Group> getGroups(String list) throws Exception {
		try {
			Vector<Group> groups = new Vector<Group>();
			Scanner myScan = new Scanner(list);
			while (myScan.hasNext()) {
				String s = myScan.next();
				s = s.substring(0, s.length() - 1);
				int i = Integer.parseInt(s);
				Group temp = getGroup(i);
				groups.add(temp);
			}
			return groups;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<FreeTimeEvent> getFreeTimeEvents(String list)
			throws Exception {
		try {
			Vector<FreeTimeEvent> ft = new Vector<FreeTimeEvent>();
			Scanner myScan = new Scanner(list);
			while (myScan.hasNext()) {
				String s = myScan.next();
				s = s.substring(0, s.length() - 1);
				int i = Integer.parseInt(s);
				FreeTimeEvent temp = getFreeTimeEvent(i);
				ft.add(temp);
			}
			return ft;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// get the list from the groups
	public String getPeopleList(Vector<Person> a) {
		String res = "";
		for (Person p : a) {
			if (p == null)
				continue;
			
			res += " " + p.getID() + ",";
		}
		return res;
	}

	public String getGroupList(Vector<Group> a) {
		String res = "";
		for (Group g : a) {
			if (g == null)
				continue;
			res += " " + g.getID() + ",";
		}
		return res;
	}

	public String getFreeTimeEventList(Vector<FreeTimeEvent> a) {
		String res = "";
		if (a == null)
			return "";
		for (FreeTimeEvent g : a) {
			if (g == null)
				continue;
			
			res += " " + g.getID() + ",";
		}
		return res;
	}

	// get all of a certain type
	public Vector<Administrator> getAllAdmins() throws Exception {
		try {
			Vector<Administrator> res = new Vector<Administrator>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM admins");
			while (result.next()) {
				res.add(getAdmin(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Volunteer> getAllVolunteers() throws Exception {
		try {
			Vector<Volunteer> res = new Vector<Volunteer>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM volunteers");
			while (result.next()) {
				res.add(getVolunteer(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Person> getAllPeople() throws Exception {
		try {
			Vector<Person> res = new Vector<Person>();

			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM admins");
			while (result.next()) {
				res.add(getAdmin(Integer.parseInt(result.getString("id"))));
			}

			stat = connection.createStatement();
			result = stat.executeQuery("SELECT * FROM volunteers");
			while (result.next()) {
				res.add(getVolunteer(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Event> getAllEvents() throws Exception {
		try {
			Vector<Event> res = new Vector<Event>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM events");
			while (result.next()) {
				res.add(getEvent(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	/**
	 * Returns all events that haven't happened yet.  For recurring events, only returns the first one that hasn't happened yet in the pattern.
	 * @return
	 * @throws Exception
	 */
	public Vector<Event> getAllFutureEvents() throws Exception {
		try {
			Vector<Event> res = new Vector<Event>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM events WHERE startTime > CURRENT_TIMESTAMP");
			while (result.next()) {
				Event adding = getEvent(Integer.parseInt(result.getString("id")));
				if(adding.getRecurrence()!=null && adding.getRecurrence()!="")
				{
					boolean add = true;
					for(Event e : res)
					{
						if(e==null) continue;
						if(e.getRecurrence().equals(adding.getRecurrence()) && e.getName().equals(adding.getName()))
						{
							add = false;
							break;
						}
					}
					if(add) res.add(adding);
				}
				else
					res.add(adding);
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<FreeTimeEvent> getAllFreeTimeEvents() throws Exception {
		try {
			Vector<FreeTimeEvent> res = new Vector<FreeTimeEvent>();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM freeTimeEvents");
			while (result.next()) {
				res.add(getFreeTimeEvent(Integer.parseInt(result
						.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<EventTemplate> getAllEventTemplates() throws Exception {
		try {
			Vector<EventTemplate> res = new Vector<EventTemplate>();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM eventTemplates");
			while (result.next()) {
				res.add(getEventTemplate(Integer.parseInt(result
						.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Group> getAllGroups() throws Exception {
		try {
			Vector<Group> res = new Vector<Group>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM groups");
			while (result.next()) {
				res.add(getGroup(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Reminder> getAllReminders() throws Exception {
		try {
			Vector<Reminder> res = new Vector<Reminder>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM reminders");
			while (result.next()) {
				res.add(getReminder(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public Vector<ReminderTemplate> getAllReminderTemplates() throws Exception {
		try {
			Vector<ReminderTemplate> res = new Vector<ReminderTemplate>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM reminderTemplates");
			while (result.next()) {
				res.add(getReminderTemplate(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public Vector<LogFileEntry> getAllLogFileEntries() throws Exception {
		try {
			Vector<LogFileEntry> res = new Vector<LogFileEntry>();
			Statement stat = connection.createStatement();
			ResultSet result = stat.executeQuery("SELECT * FROM logFileEntries");
			while (result.next()) {
				res.add(getLogFileEntry(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// deletion commands
	public boolean delete(Person a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM admins WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM admins WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}

			result = stat
					.executeQuery("SELECT * FROM volunteers WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM volunteers WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}

			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public boolean delete(Event a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM events WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				Vector<Event> events = getAllEvents();
				//System.out.println( "start events" );
				for( Event ev : events ){
					
					if ( ev.getNextEventID() == a.getId() ){
						//System.out.println( "found it" );
						//System.out.println( ev );
						stat.executeUpdate("DELETE FROM events WHERE id LIKE '"
						+ a.getID() + "'");
						ev.setNextEventID( a.getNextEventID() );
						write( ev );
						
						return true;
					}
					if ( ev.getNextEventID() == -1 ){
						stat.executeUpdate("DELETE FROM events WHERE id LIKE '"
								+ a.getID() + "'");
//						instance.write(new LogFileEntry(null, "DELETE: " + a));
						return true;
					}
				}
				
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public boolean delete(FreeTimeEvent a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM freeTimeEvents WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM freeTimeEvents WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public boolean delete(EventTemplate a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM eventTemplates WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM eventTemplates WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public boolean delete(Group a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM groups WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM groups WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public boolean delete(Reminder a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminders WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM reminders WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public boolean delete(ReminderTemplate a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminderTemplates WHERE id LIKE '"
							+ a.getID() + "'");
			if (result.next()) {
				stat.executeUpdate("DELETE FROM reminderTemplates WHERE id LIKE '"
						+ a.getID() + "'");
//				instance.write(new LogFileEntry(null, "DELETE: " + a));
				return true;
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// special case getters
	public Vector<Reminder> getReminders(Event e) throws Exception {
		try {
			Vector<Reminder> res = new Vector<Reminder>();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM reminders WHERE eventID LIKE '"
							+ e.getID() + "'");
			while (result.next()) {
				res.add(getReminder(Integer.parseInt(result.getString("id"))));
			}

			return res;
		}
		catch (SQLException e1) {
			instance.write(new LogFileEntry(-2, "server", e1.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e1.getMessage());
		}
	}

	public Vector<Event> getEvents(Group g) throws Exception {
		try {
			Vector<Event> res = new Vector<Event>();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM events WHERE groupIDList LIKE '% "
							+ g.getID() + ",%'");
			while (result.next()) {
				res.add(getEvent(Integer.parseInt(result.getString("id"))));
			}
			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	/**
	 * 
	 * @param p
	 *            -Get all of the events to which this person belongs
	 * @param includeGroups
	 *            -If true, search for events that this person belongs to by
	 *            virtue of being a member of a group. If false, include only
	 *            events that the person belongs to explicitly.
	 * @return List of events to which the person belongs
	 * @throws Exception
	 */
	public Vector<Event> getEvents(Person p, boolean includeGroups)
			throws Exception {
		try {
			Vector<Event> res = new Vector<Event>();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM events WHERE personIDList LIKE '% "
							+ p.getID() + ",%'");
			while (result.next()) {
				res.add(getEvent(Integer.parseInt(result.getString("id"))));
			}
			if (includeGroups == false)
				return res;

			// add in events from groups
			Vector<Group> myGroups = getGroups(p);
			for (Group g : myGroups) {
				if (g == null)
					continue;
				
				Vector<Event> groupEvents = getEvents(g);
				for (Event e : groupEvents) {
					if (e == null)
						continue;
					
					int thisID = e.getID();
					boolean contains=false;
					
					for(Event resE : res)
					{
						if(resE==null) continue;
						if(thisID == resE.getId())
							contains = true;
					}
					if(!contains)
						res.add(e);
				}
			}

			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Event> getEvents(Person p) throws Exception {
		try {
			return getEvents(p, true);
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Vector<Group> getGroups(Person p) throws Exception {
		try {
			Vector<Group> res = new Vector<Group>();
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT * FROM groups WHERE personIDList LIKE '% "
							+ p.getID() + ",%'");
			while (result.next()) {
				res.add(getGroup(Integer.parseInt(result.getString("id"))));
			}

			return res;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Reminder getNextReminder() throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT id,eventID, time FROM reminders WHERE time =(SELECT MIN(time) FROM reminders WHERE time > CURRENT_TIMESTAMP)");
			if (result.next()) {
				return getReminder(Integer.parseInt(result.getString("id")));
			}
			return null;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public Event getNextEvent() throws Exception {
		try {
			Statement stat = connection.createStatement();
			ResultSet result = stat
					.executeQuery("SELECT id, starttime FROM events WHERE starttime =(SELECT MIN(starttime) FROM reminders WHERE starttime > CURRENT_TIMESTAMP)");
			if (result.next()) {
				return getEvent(Integer.parseInt(result.getString("id")));
			}
			return null;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	// write commands
	public int write(Administrator a) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			if (inDB(a)) {
				String update = "";
				update += " firstName='" + clean(a.getFirstName()) + "',";
				update += " lastName='" + clean(a.getLastName()) + "',";
				update += " email='" + clean(a.getEmail()) + "',";
				update += " carrier='" + clean(a.getCarrier()) + "',";
				update += " cellPhoneNumber='" + clean(a.getCellPhoneNumber())
						+ "',";
				update += " smsEmail='" + clean(a.getSmsEmail()) + "',";
				update += " username='" + clean(a.getUsername()) + "',";
				update += " password='" + clean(a.getPassword()) + "',";
				update += " FreeTimeEventList='"
						+ getFreeTimeEventList(a.getFreeTimeEvents()) + "',";
				if (a.getNotifyOnNewMember())
					update += " notifyOnNewMember='1'";
				else
					update += " notifyOnNewMember='0'";
				stat.executeUpdate("UPDATE admins SET" + update + " WHERE id="
						+ a.getID() + "");
				id = a.getID();
			}
			else {
				int i;
				if (a.getNotifyOnNewMember())
					i = 1;
				else
					i = 0;
				id = instance.getNextID();
				stat.executeUpdate("INSERT INTO admins VALUES ('" + id
						+ "','" + clean(a.getFirstName()) + "','"
						+ clean(a.getLastName()) + "','" + clean(a.getEmail())
						+ "','" + clean(a.getCarrier()) + "','"
						+ clean(a.getCellPhoneNumber()) + "','"
						+ clean(a.getSmsEmail()) + "','"
						+ clean(a.getUsername()) + "','"
						+ clean(a.getPassword()) + "','"
						+ getFreeTimeEventList(a.getFreeTimeEvents()) + "','"
						+ i + "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public int write(Volunteer a) throws Exception
	{
		return write(a, null);
	}
	
	private int write(Volunteer a, String c) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			if (inDB(a)) {
				String update = "";
				update += " firstName='" + clean(a.getFirstName()) + "',";
				update += " lastName='" + clean(a.getLastName()) + "',";
				update += " email='" + clean(a.getEmail()) + "',";
				update += " carrier='" + clean(a.getCarrier()) + "',";
				update += " cellPhoneNumber='" + a.getCellPhoneNumber() + "',";
				update += " smsEmail='" + clean(a.getSmsEmail()) + "',";
				update += " username='" + clean(a.getUsername()) + "',";
				update += " password='" + clean(a.getPassword()) + "',";
				if(c!=null) update += " regCode='" + clean(c) + "',";
				update += " FreeTimeEventList='"
						+ getFreeTimeEventList(a.getFreeTimeEvents()) + "'";
				// System.out.println("UPDATE volunteers SET" + update +
				// " WHERE id="+a.getID()+"");
				if(c!=null)stat.executeUpdate("UPDATE tempUsers SET" + update
						+ " WHERE id=" + a.getID() + "");
				else
				stat.executeUpdate("UPDATE volunteers SET" + update
						+ " WHERE id=" + a.getID() + "");
				id = a.getID();
			}
			else {
				id = instance.getNextID();
				if(c==null)
				stat.executeUpdate("INSERT INTO volunteers VALUES ('"
						+ id + "','" + clean(a.getFirstName()) + "','"
						+ clean(a.getLastName()) + "','" + clean(a.getEmail())
						+ "','" + clean(a.getCarrier()) + "','"
						+ clean(a.getCellPhoneNumber()) + "','"
						+ clean(a.getSmsEmail()) + "','"
						+ clean(a.getUsername()) + "','"
						+ clean(a.getPassword()) + "','"
						+ getFreeTimeEventList(a.getFreeTimeEvents()) + "')");
				else
					stat.executeUpdate("INSERT INTO tempUsers VALUES ('"
							+ id + "','" + clean(a.getFirstName()) + "','"
							+ clean(a.getLastName()) + "','" + clean(a.getEmail())
							+ "','" + clean(a.getCarrier()) + "','"
							+ clean(a.getCellPhoneNumber()) + "','"
							+ clean(a.getSmsEmail()) + "','"
							+ clean(a.getUsername()) + "','"
							+ clean(a.getPassword()) + "','"
							+ getFreeTimeEventList(a.getFreeTimeEvents()) + "','"
							+ clean(c) + "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public int write(Event a) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			if (inDB(a)) {
				String update = "";
				update += " name='" + clean(a.getName()) + "',";
				update += " description='" + clean(a.getDescription()) + "',";
				update += " startTime='"
						+ dateFormat.format((a.getStartTime())) + "',";
				update += " endTime='" + dateFormat.format((a.getEndTime()))
						+ "',";
				update += " recurrence='" + clean(a.getRecurrence()) + "',";
				update += " nextEventID='" + a.getNextEventID() + "',";
				update += " groupIDList='" + getGroupList(a.getGroups()) + "',";
				update += " personIDList='" + getPeopleList(a.getPeople())
						+ "'";
				stat.executeUpdate("UPDATE events SET" + update + " WHERE id="
						+ a.getID() + "");
				id = a.getID();
			}
			else {
					id = instance.getNextID();
					stat.executeUpdate("INSERT INTO events VALUES ('" +id+ "','" 
						+ clean(a.getName()) + "','"
						+ clean(a.getDescription()) + "','"
						+ dateFormat.format((a.getStartTime())) + "','"
						+ dateFormat.format((a.getEndTime())) + "','"
						+ clean(a.getRecurrence()) + "','" + a.getNextEventID()
						+ "','" + getGroupList(a.getGroups()) + "','"
						+ getPeopleList(a.getPeople()) + "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public int write(FreeTimeEvent a) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			if (inDB(a)) {
				String update = "";
				update += " name='" + clean(a.getName()) + "',";
				update += " description='" + clean(a.getDescription()) + "',";
				update += " startTime='"
						+ dateFormat.format((a.getStartTime())) + "',";
				update += " endTime='" + dateFormat.format((a.getEndTime()))
						+ "',";
				update += " recurrence='" + clean(a.getRecurrence()) + "',";
				update += " nextEventID='" + a.getNextEventID() + "',";
				update += " personID='" + a.getPerson() + "',";
				stat.executeUpdate("UPDATE events SET" + update + " WHERE id="
						+ a.getID() + "");
				id = a.getId();
			}
			else {
					id = instance.getNextID();
					stat.executeUpdate("INSERT INTO events VALUES ('" + id
						+ "','" + clean(a.getName()) + "','"
						+ clean(a.getDescription()) + "','"
						+ dateFormat.format((a.getStartTime())) + "','"
						+ dateFormat.format((a.getEndTime())) + "','"
						+ clean(a.getRecurrence()) + "','" + a.getNextEventID()
						+ "','" + a.getPerson() + "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public int write(EventTemplate a) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			if (inDB(a)) {
				String update = "";
				update += " name='" + clean(a.getName()) + "',";
				update += " description='" + clean(a.getDescription()) + "',";
				update += " startTime='"
						+ dateFormat.format((a.getStartTime())) + "',";
				update += " endTime='" + dateFormat.format((a.getEndTime()))
						+ "',";
				update += " recurrence='" + clean(a.getRecurrence()) + "',";
				update += " groupIDList='" + getGroupList(a.getGroups()) + "',";
				update += " personIDList='" + getPeopleList(a.getPeople())
						+ "'";
				stat.executeUpdate("UPDATE eventTemplates SET" + update
						+ " WHERE id=" + a.getID() + "");
				id = a.getId();
			}
			else {
				id = instance.getNextID();
				stat.executeUpdate("INSERT INTO eventTemplates VALUES ('"
						+ id + "','" + clean(a.getName()) + "','"
						+ clean(a.getDescription()) + "','"
						+ dateFormat.format((a.getStartTime())) + "','"
						+ dateFormat.format((a.getEndTime())) + "','"
						+ clean(a.getRecurrence()) + "','"
						+ getGroupList(a.getGroups()) + "','"
						+ getPeopleList(a.getPeople()) + "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public int write(Group a) throws Exception {
		int id;
		try {
			int isPublic = a.isPublic() ? 1 : 0;
			Statement stat = connection.createStatement();
			if (inDB(a)) {
				String update = "";
				update += " name='" + clean(a.getName()) + "',";
				update += " personIDList='" + getPeopleList(a.getPersonList())+ "',";
				update += " description='" + clean(a.getDescription())+ "',";
				update += " isPublic='" + isPublic
						+ "'";
				stat.executeUpdate("UPDATE groups SET" + update + " WHERE id="
						+ a.getID() + "");
				id = a.getID();
			}
			else {
				id = instance.getNextID();
				stat.executeUpdate("INSERT INTO groups VALUES ('" + id
						+ "','" + clean(a.getName()) + "','"
						+ getPeopleList(a.getPersonList()) + "','"
						+ clean(a.getDescription())+ "','"
						+ isPublic
						+ "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	public int write(Reminder a) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			DatabaseHandler.change = true;
			//System.out.println("Change set: " + DatabaseHandler.change);
			int sent = a.isSent() ? 1 : 0;
			int email = a.isEmail() ? 1 : 0;
			int SMS = a.isSMS() ? 1 : 0;
			if (inDB(a)) {
				String update = "";
				update += " eventID='" + a.getEvent().getID() + "',";
				update += " time='" + dateFormat.format((a.getTime())) + "',";
				update += " groupIDList='" + getGroupList(a.getGroups()) + "',";
				update += " personIDList='" + getPeopleList(a.getPeople())
						+ "',";
				update += " message='" + clean(a.getMessage()) + "',";
				update += " sent='" + sent + "',";
				update += " email='" + email + "',";
				update += " SMS='" + SMS + "'";
				stat.executeUpdate("UPDATE reminders SET" + update
						+ " WHERE id=" + a.getID() + "");
				id = a.getID();
			}
			else {
				id = instance.getNextID();
				stat.executeUpdate("INSERT INTO reminders VALUES ('"
						+ id + "','" + a.getEvent().getID() + "','"
						+ dateFormat.format((a.getTime())) + "','"
						+ getGroupList(a.getGroups()) + "','"
						+ getPeopleList(a.getPeople()) + "','"
						+ clean(a.getMessage()) + "','" 
						+ sent + "','"
						+ email + "','"
						+ SMS
						+ "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public int write(ReminderTemplate a) throws Exception {
		int id;
		try {
			Statement stat = connection.createStatement();
			int email = a.isEmail() ? 1 : 0;
			int SMS = a.isSMS() ? 1 : 0;
			if (inDB(a)) {
				String update = "";
				update += " name='" + clean(a.getName()) + "',";
				update += " time='" + a.getTimeBeforeEvent() + "',";
				update += " groupIDList='" + getGroupList(a.getGroups()) + "',";
				update += " personIDList='" + getPeopleList(a.getPeople())
						+ "',";
				update += " message='" + clean(a.getMessage()) + "',";
				update += " email='" + email + "',";
				update += " SMS='" + SMS + "'";
				stat.executeUpdate("UPDATE reminderTemplates SET" + update
						+ " WHERE id=" + a.getID() + "");
				id = a.getID();
			}
			else {
				id = instance.getNextID();
				stat.executeUpdate("INSERT INTO reminderTemplates VALUES ('"
						+ id + "','" + clean(a.getName()) + "','"
						+ a.getTimeBeforeEvent() + "','"
						+ getGroupList(a.getGroups()) + "','"
						+ getPeopleList(a.getPeople()) + "','"
						+ clean(a.getMessage()) + "','" 
						+ email + "','"
						+ SMS
						+ "')");
			}
//			instance.write(new LogFileEntry(null, "WRITE: " + a));
			return id;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	public boolean writeLogEntry(int sessionID, String message, boolean devOnly)
	{
		try{
			LogFileEntry l = new LogFileEntry(sessionID, getUserFromSessionId(sessionID).getUsername(), message, devOnly);
			write(l);
			return true;
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
	}
	
	private int write(LogFileEntry a) throws Exception {
		try {
			Statement stat = connection.createStatement();
			int id = instance.getNextID();
			int devOnly = a.isDeveloperOnly() ? 1 : 0;
			stat.executeUpdate("INSERT INTO logFileEntries VALUES ('"
					+ id + "','" + 
					+ a.getSessionID() + "','" + 
					a.getUser() + "','"
					+ dateFormat.format((a.getTime())) + "','"
					+ clean(a.getLogAction()) + "','"
					+ devOnly + 
					"')");	
			return id;
		}
		catch (SQLException e) {
			//instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}

	
	private int getNextAvailableID() {
		int max = 0;

		Statement stat;
		try {
			stat = connection.createStatement();

			for (String table : tables) {
				if (table == null)
					continue;
				
				ResultSet result = stat.executeQuery("SELECT MAX(id) FROM "
						+ table);
				if (result.next()) {
					int temp = result.getInt(1);
					if (temp > max) {
						max = temp;
					}
				}
			}
		}
		catch (SQLException e) {
			System.out.println("SQL Exception occurred: " + e.getMessage());
		}

		if(max > 1847483646) instance.idOverflow = true;
		return max;
	}

	public DatabaseHandler getDB() {
		return instance;
	}

	private void sendUnsentReminders() throws Exception {
		// first, deal with any unsent reminders
		Statement stat;
		try {
			stat = instance.connection.createStatement();

			ResultSet result = stat
					.executeQuery("SELECT * FROM reminders WHERE time < CURRENT_TIMESTAMP AND sent=0");
			while (result.next()) {
				Reminder r = instance.getReminder(result.getInt("id"));
				if(Mail.sendMessage(r))
					r.setSent(true);
				write(r);
				System.out.println("sent unsent reminder " + r);
			}
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			e.printStackTrace();
		}
		catch (Exception e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			e.printStackTrace();
		}
	}
	
	public boolean sendSMS(Vector<Person> people, String message, String subject)
	{
		System.out.println("sending SMS to " + people);
		return Mail.sendSMS(people, message, subject);
	}
	
	/**
	 * Converts a volunteer to an admin server-side and returns the admin
	 * @param v the volunteer
	 * @return the Admin on success, null on failure
	 * @throws Exception 
	 */
	public Administrator convertToAdmin(Volunteer v) throws Exception
	{
		try{
			
			if(!inDB(v))
				return null;
			
			instance.delete(v);
			
			Administrator a = new Administrator(v.getFirstName(), v.getLastName(), v.getEmail(), v.getCarrier(), v.getCellPhoneNumber(), v.getSmsEmail(), v.getUsername(), v.getPassword(), v.getFreeTimeEvents(), true);
			a.setId(instance.write(a));
			
			return a;
			
		}catch(SQLException e){
			throw new Exception("SQL Exception: " + e.getMessage());
		}
	}
	
	/**
	 * Sends a prewritten invitation to event to the people.  The message will be included as "Message: " message
	 * @param people
	 * @param event
	 * @param message
	 * @throws Exception
	 */
	public void sendInvitation(Vector<Person> people, Event event, String message) throws Exception
	{
		for(Person v : people)
		{
			String sending = "Hello " + v.getFirstName() + ",\n" +
					"\n" +
					"Based on free time that you listed in your account, you have been invited to the following Red Cross event:\n" +
					"\n" +
					"Event: " + event.getName() + "\n" +
					"Description: " + event.getDescription() + "\n" +
					"Time: " + event.getStartTimeString() + " to " + event.getEndTimeString() + "\n" +
					"Message: " + message + "\n" +
					"\n" +
					"\n" +
					"NOTE: This is an automatic message generated by our registration system.  To stop receiving these messages, login to your account and"
					+ "change your free time settings\n";
					
			Vector<Person> vol = new Vector<Person>();
			vol.add(v);
			Mail.sendeMail(vol, sending, "Red Cross Event Invitation: " + event.getName());
		}
	}
	
	/**
	 * Emails the given Person a confirmation email and stores their data in a temp table
	 * @param p
	 * @throws Exception 
	 */
	public void sendConfirmation(Volunteer v) throws Exception
	{
		final String baseURL = "http://127.0.0.1:8888/Server.html?gwt.codesvr=127.0.0.1:9997&code=";
		String code = new BigInteger(130, rand).toString(32);
		System.out.println("Generated code " + code + " for user " + v.getUsername());
		instance.write(v, code);
		
		String message = "Hello " + v.getFirstName() + ",\n" +
				"\n" +
				"Thank you for registering with the Tippecanoe County Red Cross Automatic Reminder System.  In order to confirm your account" +
				" please click the following link: " + baseURL + code + "\n" +
				"\n" +
				"Thank you,\n" +
				"Tippecanoe County Red Cross\n" +
				"\n" +
				"\n" +
				"NOTE: This is an automatic message generated by our registration system.\n";
				
		Vector<Person> vol = new Vector<Person>();
		vol.add(v);
		System.out.println("writing temp vol " + v);
		Mail.sendeMail(vol, message, "Registration Confirmation for Tippecanoe County Red Cross Reminder System");
	}
	
	/**
	 * Confirm whether or not the given code matches a user waiting for validation. If so, validate the user and move them to the volunteers table.
	 * @param code
	 * @return
	 * @throws Exception 
	 */
	public boolean confirm(String code) throws Exception
	{
		try {
			Statement stat = connection.createStatement();

			ResultSet result = stat
					.executeQuery("SELECT * FROM tempUsers WHERE regCode='"+clean(code)+"'");
			if (result.next()) {
				Volunteer v = instance.getVolunteer(result.getInt("id"), true);
				System.out.println("User confirmation: " + v);
				stat.executeUpdate("DELETE FROM tempUsers WHERE regCode='"+ clean(code) + "'");
				write(v);
				
				Vector<Administrator> admins = instance.getAllAdmins();
				for(Administrator a : admins)
				{
					if (a == null)
						continue;
					
					if(a.getNotifyOnNewMember())
					{
						String message = "Hello " + a.getFirstName()+ ",\n" +
							"\n" +
							"This message is to notify you that " + v.getFirstName() + " " + v.getLastName() + " has registered with" + 
							"the automatic reminder system with username " + v.getUsername() + ".\n" +
							"\n" +
							"NOTE: To disable these messages, login to the website and modify your account settings.";
						Vector<Person> temp = new Vector<Person>();
						temp.add(a);
						Mail.sendeMail(temp, message, "New user registered with the Red Cross Reminder System");
					}
				}
				
				
				return true;
			}
			return false;
		}
		catch (SQLException e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			e.printStackTrace();
		}
		catch (Exception e) {
			instance.write(new LogFileEntry(-2, "server", e.getStackTrace().toString(), true));
			e.printStackTrace();
		}
		return false;
	}

	public static void main(String[] args) {
		System.out.println("running...");
		DatabaseHandler theDatabase;
		if (args.length == 0) {
			try {
				theDatabase = DatabaseHandler.create();

				theDatabase.sendUnsentReminders();

				Reminder nextReminder = null;
				java.util.Date before;
		//		java.util.Date after;
				java.util.Date remTime = new Date(48653487);
				DatabaseHandler.change = true;
				int greatestRemID = 0, oldGreatestRemID = 0;
				// infinite loop to send Reminders
				while (true) {
					Statement stat = theDatabase.connection.createStatement();
					ResultSet result = stat.executeQuery("SELECT MAX(id) FROM reminders");
					if (result.next())
						greatestRemID = result.getInt(1);
						
					java.util.Calendar cal = java.util.Calendar.getInstance();
					before = cal.getTime();
					System.out.println("Checking for reminders, system time: "
							+ before);
					
					if(greatestRemID != oldGreatestRemID || remTime.before(before)) {
						nextReminder = theDatabase.getNextReminder();
						remTime = nextReminder.getTime();
						System.out.println("next: " + nextReminder);
						oldGreatestRemID = greatestRemID;
						theDatabase.sendUnsentReminders();
					}
					

					Thread.currentThread();
					Thread.sleep(sleepTime);

					//cal = java.util.Calendar.getInstance();
					//after = cal.getTime();
					// System.out.println("after time: " + after);

					/*if (remTime.equals(before) || remTime.equals(after)
							|| (remTime.before(after) && remTime.after(before))) {
						// need to send out the Reminder
						Mail.sendMessage(nextReminder);
						nextReminder.setSent(true);
						theDatabase.write(nextReminder);
						System.out.println("sent reminder " + nextReminder);
						theDatabase.change = true;
					}*/
				}

			}
			catch (IOException e) {
				
				e.printStackTrace();
			}
			catch (SQLException e) {
				
				e.printStackTrace();
			}
			catch (InterruptedException e) {
				
				e.printStackTrace();
			}
			catch (Exception e) {
				
				e.printStackTrace();
			}
		}
		else if (args.length > 0 && args[0].equals("new")) {
			//recreate the DB
			System.out.println("Recreate DB" + args[0]);
			DatabaseHandler newDB;
			try {
				newDB = new DatabaseHandler();
				newDB.recreateDB();
			}
			catch (IOException e) {
				
				e.printStackTrace();
			}
			catch (SQLException e) {
				
				e.printStackTrace();
			}
			catch (Exception e) {
				
				e.printStackTrace();
			}
		}
		else if ((args.length > 0) && (args[0].equals("test"))) {
			try {
				
				DatabaseHandler newDB = new DatabaseHandler();
				newDB.recreateDB();
				
				System.out.println(args[0]);

				DatabaseHandler mine = create();

				Vector<FreeTimeEvent> ft = null;

				Administrator a1 = new Administrator( "Bob",
						"Jones", "bob@test.com", "att", "7655556666",
						"testsms@test", "bob@test.com", "bobpassword", ft);
				Volunteer v1 = new Volunteer( "Jake", "Jones",
						"jake@test.com", "att", "7655556667", "testsms@test",
						"jake@test.com", "jakepassword", ft);
				Volunteer v2 = new Volunteer( "Jim", "Jones",
						"jim@test.com", "att", "7655556668", "testsms@test",
						"jim@test.com", "jimpassword", ft);
				Volunteer v4 = new Volunteer( "Kevin",
						"Barlow (Purdue)", "kbarlow@purdue.edu", "att", "7654308750",
						"7654308750@text.att.net", "kbarlow@purdue.edu", "test", ft);

				//mine.sendConfirmation(v4);
				//v4.setId(mine.write(v4));
				a1.setId(mine.write(a1));
				v1.setId(mine.write(v1));
				v2.setId(mine.write(v2));

				Vector<Volunteer> vols = mine.getAllVolunteers();
				for (Person p : vols) {
					if (p == null)
						continue;
					
					System.out.println(p);
				}

				Volunteer v3 = new Volunteer("Jessi",
						"Jones", "Jessi@test.com", "att", "wrong",
						"testsms", "Jessi@test.com", "jessipassword", ft);
				v3.setId(mine.write(v3)); // should overwrite v2 entry

				vols = mine.getAllVolunteers();
				for (Person p : vols) {
					if (p == null)
						continue;
					
					System.out.println(p);
				}

				System.out.println("deleted jake:");
				mine.delete(v1);
				vols = mine.getAllVolunteers();
				for (Person p : vols) {
					if (p == null)
						continue;
					
					System.out.println(p);
				}
				java.util.Date now = new java.util.Date();
				java.util.Date start = new java.util.Date(now.getTime()+300000);
				java.util.Date end = new java.util.Date(start.getTime()+400000);
				Vector<Person> people = new Vector<Person>();
				people.add(v2);
				people.add(v3);
				//people.add(v4);
				Vector<Group> groups = new Vector<Group>();
				Group g1 = new Group("2011 Volunteers", people, "Some people who volunteered in 2011", true);
				g1.addPersonToGroup(a1);
				g1.setId(mine.write(g1));
				System.out.println("group: " + mine.getAllGroups());
				g1.removePersonFromGroup(a1);
				mine.write(g1);
				System.out.println("group minus a1: " + mine.getAllGroups());
				
				Vector<Group> cprGroup = new Vector<Group>();
				cprGroup.add(g1);
				
				Event e1 = new Event( "CPR Training", "A class for CPR traning", start, end,
						"recur", -1, cprGroup, people);
				Event e2 = new Event( "DAT Meeting", "Disaster action team meeting", start, end,
						"recur", -1, groups, people);
				Event e3 = new Event( "Staff Meeting", "Meeting for the local Red Cross staff", start, end,
						"recur2", -1, groups, people);

				e1.setId(mine.write(e1));
				e2.setId(mine.write(e2));
				e3.setId(mine.write(e3));
				Vector<Event> events = mine.getAllEvents();
				for (Event p : events) {
					if (p == null)
						continue;
					
					System.out.println(p);
				}

				EventTemplate et1 = new EventTemplate(
						"Volunteer Meeting", "Meeting to let volunteers know about volunteering opportunities", start, end, "recurtemp", groups, people);
				et1.setId(mine.write(et1));
				System.out.println(mine.getEventTemplate(et1.getId()));

				java.util.Calendar c = java.util.Calendar.getInstance();
				c.set(2011, 4, 10);
				java.util.Date dateC = c.getTime();
				c.set(2011, 3, 7, 22, 30);
				java.util.Date dateC2 = c.getTime();

				Reminder r1 = new Reminder(e1, dateC, groups,
						people, "Don't forget the event");
				Reminder r2 = new Reminder( e1, dateC2,
						groups, people, "Don't forget the event");
				r1.setSMS(true);
				r2.setSMS(true);
				r2.setEmail(true);
				
				r1.setId(mine.write(r1));
				r2.setId(mine.write(r2));
				System.out.println(mine.getReminder(r2.getID()));

				System.out.println("getevents:" + mine.getEvents(v2));
				System.out.println("getReminders: " + mine.getReminders(e1));
				
				System.out.println("event from temp: " + new Event(et1));

				ReminderTemplate rt1 = new ReminderTemplate("30 minutes before", 30, groups, people, "Don't forget the meeting in 30 minutes!" );
				rt1.setId(mine.write(rt1));
				Vector<ReminderTemplate> rts = mine.getAllReminderTemplates();
				for (ReminderTemplate p : rts) {
					if (p == null)
						continue;
					
					System.out.println(p);
				}
				
			//	System.out.println("reminder from temp: " + new Reminder(rt1, e1));
				
				int id = mine.validPassword("Bob@test.com", mine.encrypt("bobpassword"));
				System.out.println("logged bob in with sessionid " + id);
				
				if(mine.checkWithServerIfSessionIdIsStillLegal(id))
					System.out.println("bobs session is still valid");
				if(!mine.checkWithServerIfSessionIdIsStillLegal(100))
					System.out.println("a random session is not valid");
				
				mine.writeLogEntry(id, "test log entry", false);
				Vector<LogFileEntry> logs = mine.getAllLogFileEntries();
				System.out.println("log files: " + logs);
				
				System.out.println("getting person from session id " + id + " : " + mine.getUserFromSessionId(id));
				
				System.out.println("future events:" + mine.getAllFutureEvents());
				
				Vector<Person> me = new Vector<Person>();
				me.add(v4);
				//Mail.sendSMS(me, "test mes", "test subj");
				//mine.forgotPassword(v4.getUsername());

			}
			catch (IOException e) {
				
				e.printStackTrace();
			}
			catch (SQLException e) {
				
				e.printStackTrace();
			}
			catch (Exception e) {
				
				e.printStackTrace();
			}
		}
	}

}
